<?php
namespace App\Services\System;

use App\Models\BasicInfoAnnualAssessment;
use App\Models\Teachers;
use App\Models\BasicInfoPosition;
use App\Models\DictionaryItem;
use Carbon\Carbon;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;
use App\Http\Controllers\Excel\ExcelExport;

class TeacherService
{
    /**
     * @var \App\Repositories\System\TeacherRepository $teacherRepository
     */
    private $teacherRepository;

    public function __construct()
    {
        $this->teacherRepository = app('teacherRepository');
    }

    /**
     * @Description: 获取教师列表信息数据
     * @Author: kzj
     * @datetime: 2022-11-21
     */
    public function getList($request)
    {
        $page = $request['page'] ?? 1;
        $limit = $request['limit'] ?? 10;
        // dd($request, 1);
        $subjectId = '';
        $genderId = '';
        $termId = 0;
        $titleTeacherId = '';
        $titleTeacherRankId = '';
        $academicId = '';
        $workDate = [];
        $filters = [];
        if (isset($request['search']['name']) && !empty($request['search']['name'])){
            $filters[] = ['name', 'like', '%'.$request['search']['name'].'%'];
        }
        if (isset($request['search']['subject']) && !empty($request['search']['subject'])){
            $subjectId = $request['search']['subject'];
        }
        if (isset($request['search']['gender']) && !empty($request['search']['gender'])){
            $genderId = $request['search']['gender'];
        }
        if (isset($request['search']['title_teacher']) && !empty($request['search']['title_teacher'])){
            $titleTeacherId = $request['search']['title_teacher'];
        }
        if (isset($request['search']['title_teacher_rank']) && !empty($request['search']['title_teacher_rank'])){
            $titleTeacherRankId = $request['search']['title_teacher_rank'];
        }
        if (isset($request['search']['academic']) && !empty($request['search']['academic'])){
            $academicId = $request['search']['academic'];
        }
        if (isset($request['search']['age']) && !empty($request['search']['age'])){
            $searchNow = Carbon::now();
            $subYear = (int) $request['search']['age'];
            $workDate = [$searchNow->subYear($subYear)->toDateTimeString(), $searchNow->subYear()->toDateTimeString()];
        }
        if (isset($request['search']['data_version_id'] )&& !empty($request['search']['data_version_id'])){
            $termId = $request['search']['data_version_id'];
        }

        $now = now();
        $user = Auth::user();
        $where = [];
        if($user->curr_role_id == 2){
            $where[] = ['user_id','=',$user->id];
        }

        $isChecker = $user->is_checker;
        $isViewAll = false;
        if($isChecker || is_check(206) || is_check(308) || is_check(309)) {
            $isViewAll = true;
        }
        // dd($isChecker);
        // dd($now->firstOfYear()->toDateTimeString(), $now->lastOfYear()->addDay()->toDateTimeString());
        // dd($now->format('Y'), $now->firstOfYear()->toDateTimeString(), $now->lastOfYear()->toDateTimeString());
        //  dd($now->format('Y'), $now->firstOfYear()->toDateTimeString(), $now->lastOfYear()->addDay()->toDateTimeString());

        // dd($workDate);
        // dd($filters);
        // 12405	非在编在岗人员
        // 11801	临时账号
        // 12868	区内轮岗教师
        // 17564	已退休离职人员
        $result = Teachers::select('id', 'user_id', 'id_no', 'name', 'data_version_id', 'birthday', 'work_date', 'major_subject', 'gender', 'political', 'nation', 'native_place', 'last_group_id', 'teaching_date', 'unit_date')
            ->where($where)
            ->where('deleted', 0)
            ->where('last_group_id', '>', 0)
            ->where('user_id', '>', 0)
            ->whereNotIn('last_group_id', [11801, 12868, 17564])
            ->whereHas('user', function($q) {
                $q->where('disabled', 0);
            })
            ->when(!empty($filters), function($q) use($filters) {
                $q->where($filters);
            })
            ->when($subjectId, function($q) use($subjectId){
                $q->whereRaw(" JSON_EXTRACT(`major_subject`,'$[0].optionId') = $subjectId");
            })
            ->when($genderId, function($q) use($genderId){
                $q->whereRaw(" JSON_EXTRACT(`gender`,'$[0].optionId') = $genderId");
            })
            ->when(!empty($workDate), function($q) use($workDate){
                $q->where('work_date', '<=', $workDate[0])->where('work_date', '>', $workDate[1]);
            })
            ->when($titleTeacherId, function($q) use($titleTeacherId) {
                $q->whereHas('titleInfo', function (Builder $query) use($titleTeacherId) {
                    $query->whereRaw(" JSON_EXTRACT(`tea_title`,'$[0].optionId') = $titleTeacherId");
                });
            })
            ->when($titleTeacherRankId, function($q) use($titleTeacherRankId) {
                $q->whereHas('titleInfo', function (Builder $query) use($titleTeacherRankId) {
                    $query->whereRaw(" JSON_EXTRACT(`tea_title_rank`,'$[0].optionId') = $titleTeacherRankId");
                });
            })
            ->when($academicId, function($q) use($academicId) {
                $q->whereHas('graduatedSchool', function (Builder $query) use($academicId) {
                    $query->whereRaw(" JSON_EXTRACT(`academic`,'$[0].optionId') = $academicId");
                });
            })
            ->when(!$isViewAll, function($q) use($user) {
                $q->where('user_id', $user->id);
            })
            // ->when($termId > 0, function($q) use($termId) {
            //     $q->whereHas('term', function ($query) use($termId) {
            //         $query->where('id', $termId);
            //     });
            // })
            ->with(['term', 'titleInfo', 'graduatedSchool', 'assessment' => function($q) use($now){
                $paramDate = Carbon::create($now->year, 1, 1);
                if($now->gte($paramDate)) {
                    $startDate = $now->subYear()->firstOfYear()->toDateTimeString();
                    $endDate = $now->lastOfYear()->addDay()->toDateTimeString();
                } else {
                    $startDate = $now->subYears(2)->firstOfYear()->toDateTimeString();
                    $endDate = $now->lastOfYear()->addDay()->toDateTimeString();
                }

                //查询当年的考核情况
                // $q->where('year', '>=', $now->firstOfYear()->toDateTimeString())->where('year', '<=', $now->lastOfYear()->addDay()->toDateTimeString());
                $q->where('year', '>=', $startDate)->where('year', '<=', $endDate);
            }])
            ->orderBy('last_group_id', 'asc')
            ->orderBy('id', 'desc')
            ->paginate($limit, '*', 'page', $page)
            ->toArray();

        return $result;
    }

    /**
     * @Description: 获取教师信息数据
     * @Author: kzj
     * @datetime: 2023-03-09
     */
    public function getBaseInfo($request)
    {
        $isSelf = false;
        if(isset($request['user_id']) && !empty($request['user_id'])) {
            $userId = $request['user_id'];
        } else {
            $user = Auth::user();
            $userId = $user->id;
            $isSelf = true;
        }

        $result = Teachers::select('id', 'user_id', 'name', 'id_no', 'birthday', 'major_subject', 'gender', 'political', 'nation', 'join_date', 'mobile', 'phone_num', 'teacher_certificate_no', 'work_date', 'teacher_no', 'email', 'native_place','last_group_id', 'teaching_date', 'unit_date')
            ->where('user_id', $userId)
            ->where('deleted', 0)
            ->with(['titleInfo', 'graduatedSchool', 'address', 'assessment', 'learnHistory', 'workHistory',
                'position' => function($q) {
                    $q->with('term');
                }
            ])
            ->when($isSelf, function($q) {
                $q->with('change');
            })
            ->first();

        return $result;
    }

    public function getPosition($request)
    {

        $user = Auth::user();
        $userId = $user->id;
        $result = BasicInfoPosition::where('user_id',$userId)
                ->where('deleted', 0)
                ->when(!empty($request['search']['materials_date']),function($q) use($request){
                    return $q->whereRaw(" data_version_id in ({$request['search']['materials_date']})");
                })
                ->when(!empty($request['search']['whereStr']),function($q) use($request){
                    return $q->whereRaw($request['search']['whereStr']);
                })
                ->with('term')
                ->get()->toArray();
                return $result;
    }


    /**
     * @Description: 更新教师考核情况
     * @Author: kzj
     * @datetime: 2023-08-31
     */
    public function updateUserAssessment($request)
    {
        if(!$request['id'] || !$request['assess_value']) {
            throw new \Exception('参数缺失！');
        }
        // dd($request);
        $now = now();
        $user = Auth::user();
        $teacher = Teachers::whereId($request['id'])->first();
        // dd($now->format('Y'), $now->firstOfYear()->toDateTimeString(), $now->lastOfYear()->toDateTimeString());

        $situation = DictionaryItem::select('item_order as order','id as optionId','parent_id as parentId','item_value as selectKey','item_name as selectValue')
            ->where('code', 'SITUATION')
            ->where('item_name', $request['assess_value'])
            ->first();

        $paramDate = Carbon::create($now->year, 6, 1);
        if($now->gte($paramDate)) {
            $startOfYear = $now->subYear()->firstOfYear()->toDateTimeString();
            $lastOfYear = $now->lastOfYear()->addDay()->toDateTimeString();
        } else {
            $startOfYear = $now->subYears(2)->firstOfYear()->toDateTimeString();
            $lastOfYear = $now->lastOfYear()->addDay()->toDateTimeString();
        }
        // $startOfYear = $now->firstOfYear()->toDateTimeString();
        // $lastOfYear = $now->lastOfYear()->toDateTimeString();
        // $endOfCurrentYear = $now->lastOfYear()->addDay()->toDateTimeString();
        $userAnnualAssess = BasicInfoAnnualAssessment::where('user_id', $teacher->user_id)
            ->where('year', '>=', $startOfYear)
            ->where('year', '<', $lastOfYear)
            ->where('deleted', 0)
            ->first();

        //判定当年没有教师有考核信息就触发更新任务，更新所有老师为合格
        $yearAnnualAssess = BasicInfoAnnualAssessment::where('year', '>=', $startOfYear)
            ->where('year', '<', $lastOfYear)
            ->where('deleted', 0)
            ->first();
        if(!$yearAnnualAssess) {
            Artisan::call('UpdateCurrentYearTeacherData:assessment');
        }

        try{
            DB::beginTransaction();

            if($userAnnualAssess) {
                $result = BasicInfoAnnualAssessment::whereId($userAnnualAssess->id)->update(['situation' => json_encode([$situation->toArray()])]);
            } else {
                $result = BasicInfoAnnualAssessment::create([
                    'user_id' => $teacher->user_id,
                    'situation' => json_encode([$situation->toArray()]),
                    'year' => $lastOfYear,
                    'creator_id' => $user->id
                ]);
            }

            DB::commit();
        }catch(\Exception $e){
            DB::rollback();
            throw new \Exception($e->getMessage());
        }

        return $result;
    }

    /**
     * @Description: 批量更新教师考核情况
     * @Author: kzj
     * @datetime: 2023-08-31
     */
    public function updateUsersAssessment($request)
    {
        if(!$request['ids'] || !$request['assess_value']) {
            throw new \Exception('参数缺失！');
        }

        $now = now();
        $user = Auth::user();
        $teachers = Teachers::whereIn('id', explode(',', $request['ids']))->get();
        // dd($request,$teachers->toArray());
        // dd($now->format('Y'), $now->firstOfYear()->toDateTimeString(), $now->lastOfYear()->toDateTimeString());

        $situation = DictionaryItem::select('item_order as order','id as optionId','parent_id as parentId','item_value as selectKey','item_name as selectValue')
            ->where('code', 'SITUATION')
            ->where('item_name', $request['assess_value'])
            ->first();

        $paramDate = Carbon::create($now->year, 1, 1);
        if($now->gte($paramDate)) {
            $startOfYear = $now->subYear()->firstOfYear()->toDateTimeString();
            $lastOfYear = $now->lastOfYear()->addDay()->toDateTimeString();
        } else {
            $startOfYear = $now->subYears(2)->firstOfYear()->toDateTimeString();
            $lastOfYear = $now->lastOfYear()->addDay()->toDateTimeString();
        }
        // $startOfYear = $now->firstOfYear()->toDateTimeString();
        // $lastOfYear = $now->lastOfYear()->toDateTimeString();
        // $endOfCurrentYear = $now->lastOfYear()->addDay()->toDateTimeString();

        //判定当年没有教师有考核信息就触发更新任务，更新所有老师为合格
        $yearAnnualAssess = BasicInfoAnnualAssessment::where('year', '>=', $startOfYear)
            ->where('year', '<', $lastOfYear)
            ->where('deleted', 0)
            ->first();
        // dd($yearAnnualAssess);
        if(!$yearAnnualAssess) {
            Artisan::call('UpdateCurrentYearTeacherData:assessment');
        }

        try{
            DB::beginTransaction();

            foreach($teachers as $teacher) {
                $userAnnualAssess = BasicInfoAnnualAssessment::where('user_id', $teacher->user_id)
                    ->where('year', '>=', $startOfYear)
                    ->where('year', '<', $lastOfYear)
                    ->where('deleted', 0)
                    ->first();

                if($userAnnualAssess) {
                    $result = BasicInfoAnnualAssessment::whereId($userAnnualAssess->id)->update(['situation' => json_encode([$situation->toArray()])]);
                } else {
                    $result = BasicInfoAnnualAssessment::create([
                        'user_id' => $teacher->user_id,
                        'situation' => json_encode([$situation->toArray()]),
                        'year' => $lastOfYear,
                        'creator_id' => $user->id
                    ]);
                }
            }

            DB::commit();
        }catch(\Exception $e){
            DB::rollback();
            throw new \Exception($e->getMessage());
        }

        return $result;
    }

    /**
     * @Description: 导出教师信息列表数据
     * @Author: kzj
     * @datetime: 2022-11-23
     */
    public function export($request)
    {
        // dd($request, 1);
        $subjectId = '';
        $genderId = '';
        $termId = 0;
        $titleTeacherId = '';
        $titleTeacherRankId = '';
        $academicId = '';
        $workDate = [];
        $filters = [];
        if (isset($request['search']['name']) && !empty($request['search']['name'])){
            $filters[] = ['name', 'like', '%'.$request['search']['name'].'%'];
        }
        if (isset($request['search']['subject']) && !empty($request['search']['subject'])){
            $subjectId = $request['search']['subject'];
        }
        if (isset($request['search']['gender']) && !empty($request['search']['gender'])){
            $genderId = $request['search']['gender'];
        }
        if (isset($request['search']['title_teacher']) && !empty($request['search']['title_teacher'])){
            $titleTeacherId = $request['search']['title_teacher'];
        }
        if (isset($request['search']['title_teacher_rank']) && !empty($request['search']['title_teacher_rank'])){
            $titleTeacherRankId = $request['search']['title_teacher_rank'];
        }
        if (isset($request['search']['academic']) && !empty($request['search']['academic'])){
            $academicId = $request['search']['academic'];
        }
        if (isset($request['search']['age']) && !empty($request['search']['age'])){
            $searchNow = Carbon::now();
            $subYear = (int) $request['search']['age'];
            $workDate = [$searchNow->subYear($subYear)->toDateTimeString(), $searchNow->subYear()->toDateTimeString()];
        }
        if (isset($request['search']['data_version_id'] )&& !empty($request['search']['data_version_id'])){
            $termId = $request['search']['data_version_id'];
        }

        $now = now();
        $user = Auth::user();
        $where = [];
        if($user->curr_role_id == 2){
            $where[] = ['creator_id','=',$user->id];
        }
        $isChecker = $user->is_checker;
        $isViewAll = false;
        if($isChecker || is_check(206) || is_check(308) || is_check(309)) {
            $isViewAll = true;
        }
        // dd($isChecker,$workDate,$filters);

        // 12405	非在编在岗人员
        // 11801	临时账号
        // 12868	区内轮岗教师
        // 17564	已退休离职人员
        $lists = Teachers::select('id', 'user_id', 'id_no', 'name', 'data_version_id', 'birthday', 'work_date', 'major_subject', 'gender', 'political', 'nation', 'native_place', 'last_group_id', 'teaching_date', 'unit_date')
            ->where('deleted', 0)
            ->where($where)
            ->where('last_group_id', '>', 0)
            ->where('user_id', '>', 0)
            ->whereNotIn('last_group_id', [11801, 12868, 17564])
            ->whereHas('user', function($q) {
                $q->where('disabled', 0);
            })
            ->when(!empty($filters), function($q) use($filters) {
                $q->where($filters);
            })
            ->when($subjectId, function($q) use($subjectId){
                $q->whereRaw(" JSON_EXTRACT(`major_subject`,'$[0].optionId') = $subjectId");
            })
            ->when($genderId, function($q) use($genderId){
                $q->whereRaw(" JSON_EXTRACT(`gender`,'$[0].optionId') = $genderId");
            })
            ->when(!empty($workDate), function($q) use($workDate){
                $q->where('work_date', '<=', $workDate[0])->where('work_date', '>', $workDate[1]);
            })
            ->when($titleTeacherId, function($q) use($titleTeacherId) {
                $q->whereHas('titleInfo', function (Builder $query) use($titleTeacherId) {
                    $query->whereRaw(" JSON_EXTRACT(`tea_title`,'$[0].optionId') = $titleTeacherId");
                });
            })
            ->when($titleTeacherRankId, function($q) use($titleTeacherRankId) {
                $q->whereHas('titleInfo', function (Builder $query) use($titleTeacherRankId) {
                    $query->whereRaw(" JSON_EXTRACT(`tea_title_rank`,'$[0].optionId') = $titleTeacherRankId");
                });
            })
            ->when($academicId, function($q) use($academicId) {
                $q->whereHas('graduatedSchool', function (Builder $query) use($academicId) {
                    $query->whereRaw(" JSON_EXTRACT(`academic`,'$[0].optionId') = $academicId");
                });
            })
            ->when(!$isViewAll, function($q) use($user) {
                $q->where('user_id', $user->id);
            })
            ->with(['term', 'titleInfo', 'graduatedSchool', 'assessment' => function($q) use($now){
                $paramDate = Carbon::create($now->year, 1, 1);
                if($now->gte($paramDate)) {
                    $startDate = $now->subYear()->firstOfYear()->toDateTimeString();
                    $endDate = $now->lastOfYear()->addDay()->toDateTimeString();
                } else {
                    $startDate = $now->subYears(2)->firstOfYear()->toDateTimeString();
                    $endDate = $now->lastOfYear()->addDay()->toDateTimeString();
                }

                //查询当年的考核情况
                $q->where('year', '>=', $startDate)->where('year', '<=', $endDate);
            }])
            ->orderBy('last_group_id', 'asc')
            ->orderBy('id', 'desc')
            ->get();

        // dd($lists->toArray());

        $i = 1;
        $exportData = [];
        foreach($lists as $l) {
            $item['key'] = $i;
            $item['major_subject'] = $l['major_subject_array'] && !empty($l['major_subject_array']) ? $l['major_subject_array'][0]['selectValue'] : '';
            $item['name'] = $l->name ? $l->name : '-';
            $item['gender'] = $l->gender_set && !empty($l->gender_set) ? $l->gender_set[0]['selectValue'] : '';

            $ttif = $l->titleInfo;
            $item['title'] = $ttif && $ttif->tea_title && !empty($ttif->title_set) ? $ttif->title_set[0]['selectValue'] : '';
            $item['tea_gain_time'] = $ttif && $ttif->tea_gain_time ? $ttif->tea_gain_time : '';
            $item['rank'] = $ttif && $ttif->tea_title_rank && !empty($ttif->rank_set) ? $ttif->rank_set[0]['selectValue'] : '';
            $item['tea_employ_time'] = $ttif && $ttif->tea_employ_time ? $ttif->tea_employ_time : '';


            $item['teaching_age'] = $l->teaching_year && $l->last_group_id != 11800 ? $l->teaching_year : 0;
            $item['work_year'] = $l->work_year ? $l->work_year : 0;

            $gs = $l->graduatedSchool;
            $item['academic'] = $gs && $gs->academic && !empty($gs->academic_set) ? $gs->academic_set[0]['selectValue'] : '';

            $item['zzmm'] = $l->political_set && !empty($l->political_set) ? $l->political_set[0]['selectValue'] : '';
            $item['age'] = $l->age ? $l->age : 0;
            $item['assessment'] = $l->assessment && !empty($l->assessment) && !empty($l->assessment[0]['situation_set']) ? $l->assessment[0]['situation_set'][0]['selectValue'] : '';

            $exportData[] = $item;

            $i++;
        }

        $title = ['序号','主学科','教师姓名','性别','职称','获得时间','岗位等级','聘任日期','教龄','工龄','学历','政治面貌','年龄','考核情况'];
        array_unshift($exportData, $title);

        $fileName = md5(auth()->user()->id.time()).'.xlsx';
        Excel::store(new ExcelExport($exportData), $fileName, 'excel');

        $result['file_name'] = $fileName;

        return $result;
    }

    //*******************外部调用方法****************************************************
    public function updateOrCreateCore($where,$data)
    {
        $this->teacherRepository->saveOrCreate($where,$data);
    }

    public function deleteCore($id, $keyName = 'id'){
        return $this->teacherRepository->delete($id,$keyName);
    }
}
?>
